/* ============================================================================ */
/*                     Import and Preprocess Data                               */
/* ============================================================================ */

clear all // Clear all stored data and variables

/* Start logging */
log using "$logs\P2P_MContran_rates.log", replace

/* Import CSV file containing FinTech and bank loan data */
import delimited "$input\IFP_Mcontran_newloan_scrapped.csv"

/* ============================================================================ */
/*                      Data Cleaning and Transformation                        */
/* ============================================================================ */

/* Extract numeric rating value by removing the first letter (activity level A, B, C...) */
gen rating10 = substr(rating, 2, 4)
drop rating 
rename rating10 ratingstring

/* Convert categorical size into numeric group */
replace size = group(size)

/* Convert rating string into numeric categories */
egen rating = group(ratingstring)

/* Convert CIB ID to a standard format (9-digit string) */
tostring cib, format("%09.0f") replace

/* Assign a unique financer identifier */
gen financer = platform
replace financer = cib if platform == ""
egen financerg = group(financer)

/* ============================================================================ */
/*                   Industry Classification Adjustments                        */
/* ============================================================================ */

/* Aggregate industries into broader categories */
replace industry = "public" if inlist(industry, "P", "Q", "R", "S")  // Merge public sectors
replace industry = "DE" if inlist(industry, "D", "E")  // Merge industries D and E
replace industry = "FL" if inlist(industry, "F", "L")  // Merge industries F and L

/* Convert industry categories into numeric groups */
egen industry10 = group(industry)

/* ============================================================================ */
/*                 Generate Loan-Specific Variables                             */
/* ============================================================================ */

gen montant_initial = loan / 1000      // Convert loan amount to thousands of euros
gen tx_pret = rate                     // Interest rate variable
gen duree_pret = maturity / 12         // Convert loan maturity to years

drop year
gen year = substr(date, 1, 4)           // Extract year from date
egen years = group(year)

gen daily_date = date(date, "YMD")      // Convert to Stata date format

/* Convert daily date to quarterly date format */
gen quarterly_date = qofd(daily_date)
format quarterly_date %tq

/* Keep only relevant observations: Candidates and P2P loans */
keep if candidate == 1 | p2p == 1

/* ============================================================================ */
/*                Summary Statistics for FinTech Loans (Table 1)                */
/* ============================================================================ */

preserve
keep if p2p == 1

/* Generate unique IDs for firms and platforms */
egen id = group(siren platform)

/* Convert loan amount back to euros for summary statistics */
replace montant = montant * 1000

/* Define global macro for loan-related variables */
global loan_var `"montant_initial tx_pret duree_pret"'

/* Convert maturity back to months for consistency */
replace duree_pret = duree_pret * 12

/* Compute summary statistics */
eststo loan_stat: estpost tabstat $loan_var, s(min mean p50 max sd count) c(statistics)

/* ============================================================================ */
/*                  Label Variables for Readability                             */
/* ============================================================================ */

label var montant_initial "Loan amount (000' euro)"
label var tx_pret         "Interest rate (\%)"
label var duree_pret      "Maturity (months)"

/* Add horizontal space to labels for LaTeX table formatting */
foreach v of varlist $loan_var {
	label variable `v' `"\hspace{0.5em}`:variable label `v''"'
}

/* ============================================================================ */
/*                  Export Summary Table to LaTeX                               */
/* ============================================================================ */

esttab loan_stat using "$tables\Table_1_FinTech_loans_characteristics_new_a.tex", ///
replace nonote nogaps booktabs fragment label noobs nomtitles nonumbers ///
cells("min(fmt(2 2 0 0 2 0 2 0 2)) mean(fmt(2 2 0 0 2 0 2 0 2)) ///
p50(fmt(2 2 0 0 2 0 2 0 2)) max(fmt(2 2 0 0 2 0 2 0 2)) ///
sd(fmt(2 2 0 0 2 0 2 0 2)) count(fmt(%9.0fc))") ///
collabels("Min" "Mean" "p50" "Max" "S.D." "Count", pattern(1 1 1 1 1 1) ///
prefix(\multicolumn{@span}{c}{) suffix(}) span ) ///
refcat(montant_initial "\emph{Loan terms}", nolabel)

restore

/* ============================================================================ */
/*        Table 3: Comparing FinTech and Bank Loans (Regression Analysis)       */
/* ============================================================================ */

/* Handle missing values in industry and rating variables */
egen ratings = group(rating)
replace ratings = 99 if missing(ratings)
replace industry10 = 99 if missing(industry10)

/* ============================================================================ */
/*             Regression Models for Loan Characteristics                        */
/* ============================================================================ */

/* fill NA in industry10 and ratings to avoid losing observations when absorbing */
egen ratings = group(rating)
replace ratings = 99 if ratings==.
replace industry10 = 99 if industry10==.
			
eststo size: reg montant_initial p2p, a(quarterly_date)
estadd local year "Y", replace
estadd local industry "N", replace 
estadd local region "N", replace 
estadd local size "N", replace 
estadd local rating "N", replace 
estadd local space "", replace
estadd local unit "EUR Mns", replace
estadd local loans "All", replace

eststo sizefe: reghdfe montant_initial p2p, a(quarterly_date i.industry10 i.ratings i.size i.region)
estadd local year "Y", replace
estadd local industry "Y", replace 
estadd local region "Y", replace 
estadd local size "Y", replace 
estadd local rating "Y", replace 
estadd local space "", replace
estadd local unit "EUR Mns", replace
estadd local loans "All", replace

eststo term: reg duree_pret p2p, a(quarterly_date)
estadd local year "Y", replace
estadd local industry "N", replace 
estadd local region "N", replace 
estadd local size "N", replace 
estadd local rating "N", replace 
estadd local space "", replace
estadd local unit "Years", replace
estadd local loans "All", replace

eststo termfe: reghdfe duree_pret p2p,  a(quarterly_date i.industry10 i.ratings i.size i.region)
estadd local year "Y", replace
estadd local industry "Y", replace 
estadd local region "Y", replace 
estadd local size "Y", replace 
estadd local rating "Y", replace 
estadd local space "", replace
estadd local unit "Years", replace
estadd local loans "All", replace

eststo ratenocontrol: reg tx_pret p2p, a(quarterly_date)
estadd local year "Y", replace
estadd local industry "N", replace 
estadd local region "N", replace 
estadd local size "N", replace 
estadd local rating "N", replace 
estadd local space "", replace
estadd local unit "\%", replace
estadd local loans "All", replace

eststo rate: reg tx_pret p2p duree_pret montant_initial, a(quarterly_date)
estadd local year "Y", replace
estadd local industry "N", replace 
estadd local region "N", replace 
estadd local size "N", replace 
estadd local rating "N", replace 
estadd local space "", replace
estadd local unit "\%", replace
estadd local loans "All", replace

eststo ratefe: reghdfe tx_pret p2p duree_pret montant_initial,  a(quarterly_date i.industry10 i.ratings i.size i.region)
estadd local year "Y", replace
estadd local industry "Y", replace 
estadd local region "Y", replace 
estadd local size "Y", replace 
estadd local rating "Y", replace 
estadd local space "", replace
estadd local unit "\%", replace
estadd local loans "All", replace	

/* ============================================================================ */
/*                   Export Regression Results to LaTeX Table                   */
/* ============================================================================ */

esttab size sizefe term termfe ratenocontrol rate ratefe using "$tables\Table_2_FinTech_and_bank_loans_new.tex", ///
replace fragment gap booktabs label nonote noobs nolines ///
b(%9.2f) se(%9.2f) star(* 0.10 ** 0.05 *** 0.01) posthead(\midrule) ///
mtitles("Loan size" "Loan size" "Maturity" "Maturity" "Rate" "Rate" "Rate") ///
varlabel(p2p "FinTech "  _cons "Constant" duree_pret "Maturity" montant_initial "Loan size") ///
stats(year industry region size rating space unit loans N r2, ///
label(`"Quarter FE"' `"Industry FE"' `"Location FE"' `"Size FE"' `"Rating FE"' `" "' `"Unit"' `"Loans"' `"\midrule N"' `"R-sq"') ///
layout("\multicolumn{1}c{@}" "\multicolumn{1}c{@}" "\multicolumn{1}c{@}" "\multicolumn{1}c{@}" ///
"\multicolumn{1}c{@}" "\multicolumn{1}c{@}" "\multicolumn{1}c{@}" ///
"\multicolumn{1}c{@}" "\multicolumn{1}c{@}" "\multicolumn{1}c{@}") fmt(%9.0fc %9.2f))

/* Close the log file */
log close
	